package org.easy.tool.common;

import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetData;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.Color;
import java.awt.Font;
import java.awt.image.BufferedImage;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import static java.awt.image.BufferedImage.TYPE_INT_ARGB;

/**
 * @author Valley
 * Description
 * Created by Valley on 2023/8/9 21:01
 */
public class ExcelUtil {


    /**
     * 功能：Excel只保留某一个sheet
     */
    public static void saveSheet(XSSFWorkbook workbook, String sheetName) {
        int num = workbook.getNumberOfSheets();
        List<Integer> indexList = new ArrayList<>();
        for (int i = 0; i < num; i++) {
            String sheetAt = workbook.getSheetAt(i).getSheetName();
            if (!sheetName.equals(sheetAt)){
                indexList.add(i);
            }
        }
        int len = indexList.size();
        for (int i = 0; i < len; i++) {
            int index = indexList.get(i) - i;
            workbook.removeSheetAt(index);
        }
    }

    /**
     * 功能：Excel只保留某些sheet
     */
    public static void saveSheet(XSSFWorkbook workbook, List<String> saveList) {
        int num = workbook.getNumberOfSheets();
        List<Integer> indexList = new ArrayList<>();
        for (int i = 0; i < num; i++) {
            String sheetName = workbook.getSheetAt(i).getSheetName();
            if (!saveList.contains(sheetName)) {
                indexList.add(i);
            }
        }
        int len = indexList.size();
        for (int i = 0; i < len; i++) {
            int index = indexList.get(i) - i;
            workbook.removeSheetAt(index);
        }
    }

    /**
     * 功能：Excel实现行移动, startRow是开始的坐标，size是移动多少格
     */
    public static void insertBeforeRow(int startRow, List<String> insertList, XSSFSheet sheet) {
        int size = insertList.size();
        int rowNum = sheet.getLastRowNum();
        System.out.println(sheet.getLastRowNum());
        sheet.shiftRows(startRow, rowNum, size, true, false);
        System.out.println(sheet.getLastRowNum());
        int insertRow = startRow;
        for (String insert : insertList) {
            XSSFCell cell = sheet.createRow(insertRow).createCell(0);
            cell.setCellValue(insert);
            insertRow++;
        }
    }

    /**
     * 功能：设置单元格不可见, 宽度2560左右为正常单元格
     */
    public static void setCellWidth(XSSFSheet sheet, int index, int width) {
        sheet.setColumnWidth(index, width);
    }

    /**
     * 功能：设置单元格为保留位数小数，数据为小数
     */
    public static void saveOneDigital(XSSFWorkbook workbook, XSSFCell cell, int num) {
        CellStyle percentCellStyle = workbook.createCellStyle();//创建单元格格式
        StringBuilder sb = new StringBuilder();
        if (num <= 0){
            sb.append("0");
        }else {
            sb.append("0.");
            for (int i = 0; i < num; i++) {
                sb.append("0");
            }
        }
        percentCellStyle.setDataFormat(workbook.createDataFormat().getFormat(sb.toString()));
        cell.setCellStyle(percentCellStyle);
    }

    /**
     * 功能：设置单元格为保留位数小数，数据为小数
     */
    public static CellStyle saveOneDigital(Workbook workbook, int num) {
        CellStyle percentCellStyle = workbook.createCellStyle();//创建单元格格式
        StringBuilder sb = new StringBuilder();
        if (num <= 0){
            sb.append("0");
        }else {
            sb.append("0.");
            for (int i = 0; i < num; i++) {
                sb.append("0");
            }
        }
        percentCellStyle.setDataFormat(workbook.createDataFormat().getFormat(sb.toString()));
        return percentCellStyle;
    }


    /**
     * 功能：设置单元格为百分比，数据为小数
     */
    public static void setPercentCell(XSSFWorkbook workbook, Cell cell, int num) throws Exception {
        CellStyle percentCellStyle = workbook.createCellStyle();//创建单元格格式
        StringBuilder sb = new StringBuilder();
        if (num <= 0){
            sb.append("0");
        }else {
            sb.append("0.");
            for (int i = 0; i < num; i++) {
                sb.append("0");
            }
        }
        sb.append("%");
        percentCellStyle.setDataFormat(workbook.createDataFormat().getFormat(sb.toString()));
        cell.setCellStyle(percentCellStyle);
    }

    /**
     * 功能：Excel实现数据追加
     */
    public static void appendExcel(String originExcel, String outExcel, List<List<Object>> data) throws Exception {
        FileInputStream fileInputStream = new FileInputStream(originExcel);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        FileOutputStream fileOutputStream = new FileOutputStream(outExcel);
        for (int i = 0; i < data.size(); i++) {
            XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);//新增一行
            List<Object> list = data.get(i);
            for (int j = 0; j < list.size(); j++) {
                XSSFCell cell = row.createCell(j);//行中的第“0+1”列
                Object value = list.get(j);
                if (value instanceof String) {
                    cell.setCellValue((String) value);//列中放的值
                } else if (value instanceof Double) {
                    cell.setCellValue((Double) value);
                    setPercentCell(xssfWorkbook, cell, 1);
//                    saveOneDigital(xssfWorkbook, cell, 0);
                }
            }
        }
        fileOutputStream.flush();
        xssfWorkbook.write(fileOutputStream);
        fileOutputStream.close();
    }

//    /**
//     * 功能：使用Spire Free Xls添加水印，未完成
//     */
//    public static void addWaterMarkSpire() {
//
//        //加载示例文档
//        com.spire.xls.Workbook workbook = new com.spire.xls.Workbook();
//        workbook.loadFromFile("Sample.xlsx");
//
//        //设置文本和字体大小
//        Font font = new Font("仿宋", Font.PLAIN, 40);
//        String watermark = "内部专用";
//
//        for (com.spire.xls.Worksheet sheet : (Iterable<com.spire.xls.Worksheet>) workbook.getWorksheets()) {
//            //调用DrawText() 方法插入图片
//            BufferedImage imgWtrmrk = drawText(watermark, font, Color.pink, Color.white, sheet.getPageSetup().getPageHeight(), sheet.getPageSetup().getPageWidth());
//
//            //将图片设置为页眉
//            sheet.getPageSetup().setLeftHeaderImage(imgWtrmrk);
//            sheet.getPageSetup().setLeftHeader("&G");
//
//            //将显示模式设置为Layout
//            sheet.setViewMode(com.spire.xls.ViewMode.Layout);
//        }
//
//        //保存文档
//        workbook.saveToFile("Watermark.xlsx", com.spire.xls.ExcelVersion.Version2010);
//
//    }

    private static BufferedImage drawText(String text, Font font, Color textColor, Color backColor, double height, double width) {
        //定义图片宽度和高度
        BufferedImage img = new BufferedImage((int) width, (int) height, TYPE_INT_ARGB);
        Graphics2D loGraphic = img.createGraphics();

        //获取文本size
        FontMetrics loFontMetrics = loGraphic.getFontMetrics(font);
        int liStrWidth = loFontMetrics.stringWidth(text);
        int liStrHeight = loFontMetrics.getHeight();

        //文本显示样式及位置
        loGraphic.setColor(backColor);
        loGraphic.fillRect(0, 0, (int) width, (int) height);
        loGraphic.translate(((int) width - liStrWidth) / 2, ((int) height - liStrHeight) / 2);
        loGraphic.rotate(Math.toRadians(-45));

        loGraphic.translate(-((int) width - liStrWidth) / 2, -((int) height - liStrHeight) / 2);
        loGraphic.setFont(font);
        loGraphic.setColor(textColor);
        loGraphic.drawString(text, ((int) width - liStrWidth) / 2, ((int) height - liStrHeight) / 2);
        loGraphic.dispose();
        return img;
    }


    /**
     * 功能：添加水印
     */
    public static void addWaterMark2(String excelPath, String watermark) throws Exception {

    }

    /**
     * 功能：根据文字绘制指定水印图片
     */
    public static BufferedImage createWatermarkImage(String content) {
        String[] textArray = content.split("br");
        Font font = new Font("microsoft-yahei", Font.BOLD, 12);
        int width = 100;
        int height = 100;
        BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
        // 背景透明 开始
        Graphics2D g = image.createGraphics();
        image = g.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
        g.dispose();
        // 背景透明 结束
        g = image.createGraphics();
        g.setColor(new Color(Integer.parseInt("C5CBCF", 16)));// 设定画笔颜色
        g.setFont(font);// 设置画笔字体
        g.shear(0.1, -0.26);// 设定倾斜度
        //设置字体平滑
        g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
        int y = 50;
        for (int i = 0; i < textArray.length; i++) {
            g.drawString(textArray[i], 0, y);// 画出字符串
            y = y + font.getSize();
        }
        g.dispose();// 释放画笔
        return image;
    }

    /**
     * 功能：从指定位置读取excel文件添加水印，优先推荐使用这个
     */
    public static void addWaterMark(String filePath, String text) throws Exception {
        FileInputStream fis = new FileInputStream(FileUtil.file(filePath));
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook(fis));
        OutputStream outputStream = Files.newOutputStream(new File(filePath).toPath());
        BufferedImage image = createWatermarkImage(text);
        // 导出到字节流B
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        ImageIO.write(image, "png", os);
        int pictureIdx = workbook.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);
        POIXMLDocumentPart poixmlDocumentPart = workbook.getXSSFWorkbook().getAllPictures().get(pictureIdx);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            XSSFSheet sheet = workbook.getXSSFWorkbook().getSheetAt(i);
            PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();
            String relType = XSSFRelation.IMAGES.getRelation();
            //add relation from sheet to the picture data
            PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
            //set background picture to sheet
            sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
        }
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

    /**
     * 功能：从指定位置读取excel文件添加水印，覆盖原有文件
     */
    public static void addWaterMarkImg(String filePath, String text) throws Exception {
        addWaterMarkImg(filePath, text, filePath);
    }

    /**
     * 功能：从指定位置读取excel文件添加水印，不覆盖原有文件
     */
    public static void addWaterMarkImg(String filePath, String text, String outPath) throws Exception {
        FileInputStream fis = new FileInputStream(FileUtil.file(filePath));
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook(fis));
        painWaterMark(workbook, text);
        OutputStream outputStream = Files.newOutputStream(new File(outPath).toPath());
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

    /**
     * 功能：添加文字水印图片
     */
    public static void painWaterMark(Workbook wb, String content) throws IOException {
        String imgFileName = "waterMark_photo_" + content + ".png";
        createWaterMark(content, imgFileName);

        int sheetSize = wb.getNumberOfSheets();
        for (int i = 0; i < sheetSize; i++) {
            Sheet sheet = wb.getSheetAt(i);
            CTSheetData sheetData = ((SXSSFWorkbook) wb).getXSSFWorkbook().getSheetAt(i).getCTWorksheet().getSheetData();
            //获取excel实际所占行
            int row = sheetData.getRowList().size();
            //获取excel实际所占列
            int column = sheetData.getRowArray()[0].getCList().size();
            //根据行与列计算实际所需多少水印
            putWaterRemarkToExcel(wb, sheet, imgFileName, 0, 0, 5, 5, column, row, 0, 0);
        }
    }

    /**
     * 功能：创建水印图片
     */
    public static String createWaterMark(String content, String fileName) throws IOException {
        //获取bufferedImage对象
        BufferedImage image = createWatermarkImage(content);
        String targetImagePath = Thread.currentThread().getContextClassLoader().getResource("").getPath() + fileName;
        ImageIO.write(image, "png", new File(targetImagePath));
        return targetImagePath;
    }

    /**
     * 功能：为Excel打上水印工具函数 请自行确保参数值，以保证水印图片之间不会覆盖。 在计算水印的位置的时候，并没有考虑到单元格合并的情况，请注意
     *
     * @param wb                Excel Workbook
     * @param sheet             需要打水印的Excel
     * @param waterRemarkPath   水印地址，classPath，目前只支持png格式的图片，
     *                          因为非png格式的图片打到Excel上后可能会有图片变红的问题，且不容易做出透明效果。
     *                          同时请注意传入的地址格式，应该为类似："\\excelTemplate\\test.png"
     * @param startXCol         水印起始列
     * @param startYRow         水印起始行
     * @param betweenXCol       水印横向之间间隔多少列
     * @param betweenYRow       水印纵向之间间隔多少行
     * @param XCount            横向共有水印多少个
     * @param YCount            纵向共有水印多少个
     * @param waterRemarkWidth  水印图片宽度为多少列
     * @param waterRemarkHeight 水印图片高度为多少行
     */
    public static void putWaterRemarkToExcel(Workbook wb, Sheet sheet, String waterRemarkPath, int startXCol,
                                             int startYRow, int betweenXCol, int betweenYRow, int XCount, int YCount, int waterRemarkWidth,
                                             int waterRemarkHeight) throws IOException {

        // 校验传入的水印图片格式
        if (!waterRemarkPath.endsWith("png") && !waterRemarkPath.endsWith("PNG")) {
            throw new RuntimeException("向Excel上面打印水印，目前支持png格式的图片。");
        }

        // 加载图片
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//        InputStream imageIn = new FileInputStream(waterRemarkPath);
        InputStream imageIn = Thread.currentThread().getContextClassLoader().getResourceAsStream(waterRemarkPath);
        if (null == imageIn || imageIn.available() < 1) {
            throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(1)。");
        }
        BufferedImage bufferImg = ImageIO.read(imageIn);
        if (null == bufferImg) {
            throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(2)。");
        }
        ImageIO.write(bufferImg, "png", byteArrayOut);

        // 开始打水印
        Drawing drawing = sheet.createDrawingPatriarch();

        // 按照共需打印多少行水印进行循环
        for (int yCount = 0; yCount < YCount; yCount++) {
            // 按照每行需要打印多少个水印进行循环
            for (int xCount = 0; xCount < XCount; xCount++) {
                // 创建水印图片位置
                int xIndexInteger = startXCol + (xCount * waterRemarkWidth) + (xCount * betweenXCol);
                int yIndexInteger = startYRow + (yCount * waterRemarkHeight) + (yCount * betweenYRow);
                /*
                 * 参数定义： 第一个参数是（x轴的开始节点）； 第二个参数是（是y轴的开始节点）； 第三个参数是（是x轴的结束节点）；
                 * 第四个参数是（是y轴的结束节点）； 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
                 * 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）； 第七个参数是（图片宽度，共多少列）；
                 * 第8个参数是（图片高度，共多少行）；
                 */
                ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, xIndexInteger,
                        yIndexInteger, xIndexInteger + waterRemarkWidth, yIndexInteger + waterRemarkHeight);

                Picture pic = drawing.createPicture(anchor,
                        wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
                pic.resize();
            }
        }
    }

    /**
     * 功能：读取Excel内容，获取Excel单元格数据
     */
    public static List<List<Object>> read(String path) {
        return cn.hutool.poi.excel.ExcelUtil.getReader(path).read();
    }

    /**
     * 功能：将数据生成Excel，不需要自定义sheet，不需要加header
     */
    public static void write(String path, List<List<Object>> data) {
        write(path, null, null, data);
    }

    /**
     * 功能：将数据生成Excel，不需要自定义sheet，需要加header
     */
    public static void write(String path, List<String> header, List<List<Object>> data) {
        write(path, null, header, data);
    }

    /**
     * 功能：将数据生成Excel，自定义sheet，需要加header
     */
    public static void write(String path, String sheet, List<String> header, List<List<Object>> data) {
        ExcelWriter writer = getWriter(path, sheet);
        if (header != null) {
            writer.writeHeadRow(header);
        }
        writer.write(data);
        writer.close();
    }

    /**
     * 功能：获取writer，没有sheet就用默认sheet
     */
    public static ExcelWriter getWriter(String path, String sheet) {
        ExcelWriter writer;
        if (StringUtil.isNotBlank(sheet)) {
            writer = cn.hutool.poi.excel.ExcelUtil.getWriter(path, sheet);
        } else {
            writer = cn.hutool.poi.excel.ExcelUtil.getWriter(path);
        }
        return writer;
    }

    /**
     * * 合并多个ExcelSheet,文件字符串(file.toString)集合,按顺序进行合并，合并的Excel中Sheet名称不可重复
     */
    public static void mergeExcel(List<String> files, String outPath) throws Exception {
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        // 遍历每个源excel文件，TmpList为源文件的名称集合
        for (String fromExcelName : files) {
            XSSFWorkbook fromExcel = new XSSFWorkbook(Files.newInputStream(Paths.get(fromExcelName)));
            XSSFSheet oldSheet = fromExcel.getSheetAt(0);
            XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
            copySheet(newExcelCreat, oldSheet, newSheet);
        }
        // 定义新生成的xlxs表格文件
        FileOutputStream fileOut = new FileOutputStream(outPath);
        newExcelCreat.write(fileOut);
        fileOut.flush();
        fileOut.close();
        newExcelCreat.close();
    }

    /**
     * Sheet复制
     */
    private static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        // 设置列宽
        int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
        for (int i = 0; i <= length; i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            XSSFRow oldRow = (XSSFRow) rowIt.next();
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb, oldRow, newRow);
        }
    }

    /**
     * 合并单元格
     */
    private static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    /**
     * 复制单元格
     */
    private static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        // 复制单元格样式
        newstyle.cloneStyleFrom(fromCell.getCellStyle());
        // 样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == CellType.STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == CellType.BLANK) {
            // nothing21
        } else if (fromCellType == CellType.BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == CellType.ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == CellType.FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else {
            // nothing29
        }
    }

    /**
     * 行复制功能
     */
    private static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }


}